import requests
import datetime
import pandas as pd
import pymysql
from sklearn.preprocessing import MinMaxScaler
from joblib import dump

class WeatherUtils(object):
    """天气类

    Args:
        object (_type_): _description_
    """
    def __init__(self):
        self.url = 'http://v.yiketianqi.com/api'
        self.date = [
            '2024-07-01',
            '2024-08-01',
            '2024-09-01',
            '2024-10-01',
            '2024-11-01',
            '2024-12-01',
        ]

    def get_data(self):
        """获取天气数据
        """
        data_list = []
        for d in self.date:
            conf = {
                'appid': '88249599',  # 用自己注册后的appid
                'appsecret': 'BAZizjnm',
                'version': 'history',
                'year': d[:4],
                'month': d[5:7],
                'city': '南昌'
            }
            res = requests.get(self.url + '?', params=conf)
            res_data = res.json()
            for i in res_data['data']:
                data_list.append({
                    'date': datetime.datetime.strptime(i['ymd'], '%Y-%m-%d'),
                    'bwendu': i['bwendu'],
                    'ylwendu': i['ylwendu'],
                    'tianqi': i['tianqi'],
                    'fengli': i['fengli'],
                })

            df = pd.DataFrame(data_list)
            df.to_csv('./timing/weather.csv')

class MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            db='scenic',
            port=3306,
            charset='utf8'
        )

        self.weather_data = pd.read_csv('./timing/weather.csv')

def is_holiday(self, data):
    """是否节假日
    """
    if data in ['2024-09-03', '2024-10-01', '2024-10-02', '2024-10-03', '2024-10-04', '2024-10-05', '2024-10-06', '2024-10-07', '2025-01-01', '2025-01-02', '2025-01-03']:
        return 1
    return 0

def get_scenic_data(self):
    """获取数据
    """
    cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = """
    SELECT DATE(g.create_time) as date, count(*) as count
    FROM order_user_gate_rel g
    WHERE DATE(g.create_time) < '2025-01-01' GROUP BY date
    """
    cursor.execute(sql)
    ret = cursor.fetchall()
    df = pd.DataFrame(ret)
    # print(df.head)

    # 合并天气数据
    self.weather_data['date'] = pd.to_datetime(self.weather_data['date'])
    df['date'] = pd.to_datetime(df['date'])
    # 格式转换
    df_pivot = pd.merge(self.weather_data, df, on='date')

    df_pivot.set_index('date', inplace=True)

    # 转化温度
    df_pivot['bWendu'] = df_pivot['bWendu'].str.replace("°", "").astype(int)
    df_pivot['yWendu'] = df_pivot['yWendu'].str.replace("°", "").astype(int)

    # # print(df_pivot.head)
    df_pivot['dow'] = df_pivot.index.dayofweek  # 星期几（0-6)
    df_pivot['month'] = df_pivot.index.month  # 月份
    df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)

    # 对星期几和月份进行独热编码
    df_pivot = pd.get_dummies(df_pivot, columns=['dow', 'month', 'tianqi', 'fengli'], dtype=int)

    # 归一化函数
    scaler = MinMaxScaler()
    
    feature = df_pivot[['count']]
    df_pivot['count'] = scaler.fit_transform(feature)

    #归一化天气
    weather_feature = df_pivot[['bWendu', 'yWendu']]
    dump(scaler, './timing/scaler_joblib')
    df_pivot[['bWendu', 'yWendu']] = scaler.fit_transform(weather_feature)
    dump(weather_feature, './timing/weather_feature_joblib')
    print(df_pivot.head)
    df_pivot.to_csv('./timing/scenic_data.csv')

if __name__ == '__main__':
    wu = WeatherUtils()
    wu.get_data()
    mu = MysqlUtils()
    mu.get_scenic_data()